﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using ConnectToDatabase.Model;

namespace ConnectToDatabase
{
    public class SelectData : MarshalByRefObject
    {
        public int Test() { return 0; }

        private string cmdtext;
        //private string connectionString;
        private MySqlConnection connection = null;
        private ConnectDB connectDB = new ConnectDB();

        #region Select ID
        public List<String> SelectMaLoaiSP()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaLoaiSanPham FROM dbgiaydep.loaisanpham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaLoaiSP = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaLoaiSP.Add(temp);
                }

                return dsMaLoaiSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaNhaPP()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaNhaPP FROM dbgiaydep.nhaphanphoi";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaNhaPP = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaNhaPP.Add(temp);
                }

                return dsMaNhaPP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaDauSP()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaDauSanPham FROM dbgiaydep.dausanpham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaDauSP = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaDauSP.Add(temp);
                }

                return dsMaDauSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaKhuyenMai()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaKhuyenMai FROM dbgiaydep.khuyenmai";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaKhuyenMai = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaKhuyenMai.Add(temp);
                }

                return dsMaKhuyenMai;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaPhieuTra()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaPhieuTraHang FROM dbgiaydep.phieutrahang";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaPhieuTra = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaPhieuTra.Add(temp);
                }

                return dsMaPhieuTra;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaSanPham()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaSanPham FROM dbgiaydep.sanpham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaSanPham = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaSanPham.Add(temp);
                }

                return dsMaSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaLoaiNV()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaLoaiNhanVien FROM dbgiaydep.loainhanvien";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaLoaiNV = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaLoaiNV.Add(temp);
                }

                return dsMaLoaiNV;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaNV()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaNhanVien FROM dbgiaydep.nhanvien";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaNV = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaNV.Add(temp);
                }

                return dsMaNV;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaDVBaoHanh()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaDVBaoHanh FROM dbgiaydep.dv_baohanh";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaDVBaoHanh = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaDVBaoHanh.Add(temp);
                }

                return dsMaDVBaoHanh;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaNhomSP()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaNhomSanPham FROM dbgiaydep.nhomsanpham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaNhomSP = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaNhomSP.Add(temp);
                }

                return dsMaNhomSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaPhieuNhap()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaNhapHang FROM dbgiaydep.phieunhaphang";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaPhieuNhap = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaPhieuNhap.Add(temp);
                }

                return dsMaPhieuNhap;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaHoaDon()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaHoaDon FROM dbgiaydep.hoadon";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaHoaDon = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaHoaDon.Add(temp);
                }

                return dsMaHoaDon;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectMaDangNhap()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT MaDangNhap FROM dbgiaydep.dangnhap";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaDangNhap = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaDangNhap.Add(temp);
                }

                return dsMaDangNhap;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Loai San Pham
        public List<LoaiSanPham> SelectAllLoaiSanPham()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.loaisanpham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<LoaiSanPham> dsLoaiSP = new List<LoaiSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    LoaiSanPham temp = new LoaiSanPham();
                    temp.MaLoaiSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TinhTrang = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsLoaiSP.Add(temp);
                }
                connection.Close();
                return dsLoaiSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }


        public List<LoaiSanPham> SelectAllLoaiSanPham(int TinhTrang)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.loaisanpham WHERE TinhTrang = '" + TinhTrang + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<LoaiSanPham> dsLoaiSP = new List<LoaiSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    LoaiSanPham temp = new LoaiSanPham();
                    temp.MaLoaiSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TinhTrang = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsLoaiSP.Add(temp);
                }
                connection.Close();
                return dsLoaiSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Nha Phan Phoi
        public List<NhaPhanPhoi> SelectAllNhaPhanPhoi()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.nhaphanphoi";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<NhaPhanPhoi> dsNhaPP = new List<NhaPhanPhoi>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    NhaPhanPhoi temp = new NhaPhanPhoi();
                    temp.MaNhaPP = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][1].ToString();
                    temp.TinhTrang = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsNhaPP.Add(temp);
                }
                connection.Close();
                return dsNhaPP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }


        public List<NhaPhanPhoi> SelectAllNhaPhanPhoi(int TinhTrang)
        {
            //connectDB.setConnectionString(connectDB.setConnectionString());
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.nhaphanphoi WHERE TinhTrang = '" + TinhTrang + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<NhaPhanPhoi> dsNhaPP = new List<NhaPhanPhoi>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    NhaPhanPhoi temp = new NhaPhanPhoi();
                    temp.MaNhaPP = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][1].ToString();
                    temp.TinhTrang = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsNhaPP.Add(temp);
                }
                connection.Close();
                return dsNhaPP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Dau San Pham
        public List<DauSanPham> SelectAllDauSanPham()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.dausanpham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DauSanPham> dsDauSP = new List<DauSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DauSanPham temp = new DauSanPham();
                    temp.MaDauSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaLoaiSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.MaNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.ThoiHanBan = int.Parse(ds.Tables[0].Rows[i][5].ToString());
                    temp.TinhTrang = int.Parse(ds.Tables[0].Rows[i][6].ToString());

                    dsDauSP.Add(temp);
                }
                connection.Close();
                return dsDauSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public DauSanPham SelectAllDauSanPham(string m_sMaDauSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.dausanpham WHERE MaDauSanPham = '" + m_sMaDauSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                DauSanPham temp = new DauSanPham();
                temp.MaDauSanPham = ds.Tables[0].Rows[0][0].ToString();
                temp.MaLoaiSanPham = ds.Tables[0].Rows[0][1].ToString();
                temp.TenDauSanPham = ds.Tables[0].Rows[0][2].ToString();
                temp.MaNhaPP = ds.Tables[0].Rows[0][3].ToString();
                temp.GiaBan = int.Parse(ds.Tables[0].Rows[0][4].ToString());
                temp.ThoiHanBan = int.Parse(ds.Tables[0].Rows[0][5].ToString());
                temp.TinhTrang = int.Parse(ds.Tables[0].Rows[0][6].ToString());

                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DauSanPham> SelectAllDauSanPham(int TinhTrang)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.dausanpham WHERE TinhTrang = '" + TinhTrang + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DauSanPham> dsDauSP = new List<DauSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DauSanPham temp = new DauSanPham();
                    temp.MaDauSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaLoaiSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.MaNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.ThoiHanBan = int.Parse(ds.Tables[0].Rows[i][5].ToString());
                    temp.TinhTrang = int.Parse(ds.Tables[0].Rows[i][6].ToString());

                    dsDauSP.Add(temp);
                }
                connection.Close();
                return dsDauSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        #endregion

        #region Select Nhom San Pham
        public List<NhomSanPham> SelectAllNhomSanPham()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.nhomsanpham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<NhomSanPham> dsNhomSP = new List<NhomSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    NhomSanPham temp = new NhomSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.MauSac = ds.Tables[0].Rows[i][2].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][4].ToString());

                    dsNhomSP.Add(temp);
                }
                connection.Close();
                return dsNhomSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Phieu Nhap Hang
        public List<PhieuNhapHang> SelectAllPhieuNhapHang()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.phieunhaphang";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<PhieuNhapHang> dsPhieuNhap = new List<PhieuNhapHang>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    PhieuNhapHang temp = new PhieuNhapHang();
                    temp.MaNhapHang = ds.Tables[0].Rows[i][0].ToString();
                    temp.NgayNhapHang = DateTime.Parse(ds.Tables[0].Rows[i][1].ToString());
                    temp.TongTien = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsPhieuNhap.Add(temp);
                }
                connection.Close();
                return dsPhieuNhap;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select CT Phieu Nhap Hang
        public List<CT_PhieuNhapHang> SelectAllCTPhieuNhapHang()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.ct_phieunhaphang";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<CT_PhieuNhapHang> dsCTPhieuNhap = new List<CT_PhieuNhapHang>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    CT_PhieuNhapHang temp = new CT_PhieuNhapHang();
                    temp.MaNhapHang = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.SoLuong = int.Parse(ds.Tables[0].Rows[i][2].ToString());
                    temp.GiaVon = int.Parse(ds.Tables[0].Rows[i][3].ToString());

                    dsCTPhieuNhap.Add(temp);
                }
                connection.Close();
                return dsCTPhieuNhap;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Khuyen Mai
        public List<KhuyenMai> SelectAllKhuyenMai()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.khuyenmai";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<KhuyenMai> dskhuyenmai = new List<KhuyenMai>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    KhuyenMai temp = new KhuyenMai();
                    temp.MaKhuyenMai = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.NoiDungKM = ds.Tables[0].Rows[i][2].ToString();
                    temp.PTGiamGia = int.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.NgayBatDau = DateTime.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.NgayKetThuc = DateTime.Parse(ds.Tables[0].Rows[i][5].ToString());

                    dskhuyenmai.Add(temp);
                }
                connection.Close();
                return dskhuyenmai;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public DateTime SelectAllKhuyenMai_MaxNgayKetThuc(string m_sMaDauSanPham, string m_dtNow)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT Max(dbgiaydep.khuyenmai.NgayKetThuc) FROM dbgiaydep.khuyenmai WHERE MaDauSanPham = '" + m_sMaDauSanPham + "' AND NgayKetThuc < '" + m_dtNow + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                DateTime temp = new DateTime();
                temp = DateTime.Parse(ds.Tables[0].Rows[0][0].ToString());

                connection.Close();
                return temp;
            }
            catch
            {
                return new DateTime(0, 0, 0);
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public KhuyenMai SelectKhuyenMai_NgayHienTai(string m_sMaDauSanPham, string m_dtNow)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.khuyenmai.MaKhuyenMai FROM dbgiaydep.khuyenmai WHERE MaDauSanPham = '" + m_sMaDauSanPham + "' AND NgayKetThuc >= '" + m_dtNow + "' AND NgayBatDau <= '" + m_dtNow + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                KhuyenMai temp = new KhuyenMai();
                temp.MaKhuyenMai = ds.Tables[0].Rows[0][0].ToString();
                temp.MaDauSanPham = ds.Tables[0].Rows[0][1].ToString();
                temp.NoiDungKM = ds.Tables[0].Rows[0][2].ToString();
                temp.PTGiamGia = int.Parse(ds.Tables[0].Rows[0][3].ToString());
                temp.NgayBatDau = DateTime.Parse(ds.Tables[0].Rows[0][4].ToString());
                temp.NgayKetThuc = DateTime.Parse(ds.Tables[0].Rows[0][5].ToString());

                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<String> SelectAllKhuyenMai_KiemTraKhuyenMai(string m_sMaDauSanPham, string m_dtStart, string m_dtEnd)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.khuyenmai.MaKhuyenMai FROM dbgiaydep.khuyenmai WHERE MaDauSanPham = '" + m_sMaDauSanPham
                + "' AND ((NgayKetThuc > '" + m_dtStart + "' AND NgayBatDau < '" + m_dtStart
                + "' ) OR ( NgayKetThuc > '" + m_dtStart + "' AND NgayBatDau > '" + m_dtStart + "' AND  NgayKetThuc < '" + m_dtEnd + "' AND NgayBatDau < '" + m_dtEnd
                + "') OR (NgayKetThuc > '" + m_dtEnd + "' AND NgayBatDau < '" + m_dtEnd + "'))";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaKhuyenMai = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaKhuyenMai.Add(temp);
                }

                connection.Close();
                return dsMaKhuyenMai;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }


        public DataSet SelectAllKhuyenMaiDataSet()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.khuyenmai";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<KhuyenMai> dskhuyenmai = new List<KhuyenMai>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<KhuyenMai> SelectAllKhuyenMai(int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.khuyenmai LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<KhuyenMai> dskhuyenmai = new List<KhuyenMai>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    KhuyenMai temp = new KhuyenMai();
                    temp.MaKhuyenMai = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.NoiDungKM = ds.Tables[0].Rows[i][2].ToString();
                    temp.PTGiamGia = int.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.NgayBatDau = DateTime.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.NgayKetThuc = DateTime.Parse(ds.Tables[0].Rows[i][5].ToString());

                    dskhuyenmai.Add(temp);
                }
                connection.Close();
                return dskhuyenmai;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Ma Dau San Pham
        public List<String> SelectAllDauSanPham(string MaLoaiSP, string MaNhaPP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.dausanpham.MaDauSanPham FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham WHERE dbgiaydep.loaisanpham.MaLoaiSanPham ='" + MaLoaiSP + "' AND dbgiaydep.nhaphanphoi.MaNhaPP = '" + MaNhaPP + "' AND dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsMaDauSP = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsMaDauSP.Add(temp);
                }
                connection.Close();
                return dsMaDauSP;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Danh Sach San Pham
        public DataSet SelectAllDSSanPhamDataSet()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                connection.Close();
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public DSSanPham SelectAllDSSanPham_MaNhomSP(string m_sMaNhomSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = '" + m_sMaNhomSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                DSSanPham temp = new DSSanPham();
                temp.MaNhomSanPham = ds.Tables[0].Rows[0][0].ToString();
                temp.TenDauSanPham = ds.Tables[0].Rows[0][1].ToString();
                temp.TenLoaiSanPham = ds.Tables[0].Rows[0][2].ToString();
                temp.TenNhaPP = ds.Tables[0].Rows[0][3].ToString();
                temp.GiaBan = int.Parse(ds.Tables[0].Rows[0][4].ToString());
                temp.MauSac = ds.Tables[0].Rows[0][5].ToString();
                temp.KichThuoc = float.Parse(ds.Tables[0].Rows[0][6].ToString());
                temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[0][7].ToString());

                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_LoaiSP(string m_sTenLoaiSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.loaisanpham.TenLoaiSanPham = '" + m_sTenLoaiSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_LoaiSP(string m_sTenLoaiSP, int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.loaisanpham.TenLoaiSanPham = '" + m_sTenLoaiSP + "'  LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_GiaBan(int m_iGiaBan)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.dausanpham.GiaBan = '" + m_iGiaBan + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_GiaBan(int m_iGiaBan, int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.dausanpham.GiaBan = '" + m_iGiaBan + "'  LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_KichThuoc(float m_fKichThuoc)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.KichThuoc = '" + m_fKichThuoc + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_KichThuoc(float m_fKichThuoc, int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.KichThuoc = '" + m_fKichThuoc + "' LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_TenSP(string m_sTenSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.dausanpham.TenDauSanPham = '" + m_sTenSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_TenSP(string m_sTenSP, int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.dausanpham.TenDauSanPham = '" + m_sTenSP + "' LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_NhaPP(string m_sNhaPP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhaphanphoi.TenNhaPP = '" + m_sNhaPP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_NhaPP(string m_sNhaPP, int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhaphanphoi.TenNhaPP = '" + m_sNhaPP + "' LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_MauSac(string m_sMauSac)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MauSac = '" + m_sMauSac + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham_MauSac(string m_sMauSac, int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            //cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham, dbgiaydep.sanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham AND dbgiaydep.nhomsanpham.MauSac = '" + m_sMauSac + "' LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MauSac = '" + m_sMauSac + "' LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<DSSanPham> SelectAllDSSanPham(int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            //cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham, dbgiaydep.sanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            cmdtext = "SELECT dbgiaydep.nhomsanpham.MaNhomSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.loaisanpham.TenLoaiSanPham, dbgiaydep.nhaphanphoi.TenNhaPP, dbgiaydep.dausanpham.GiaBan, dbgiaydep.nhomsanpham.MauSac, dbgiaydep.nhomsanpham.KichThuoc, dbgiaydep.nhomsanpham.SoLuongHienTai  FROM dbgiaydep.dausanpham, dbgiaydep.nhaphanphoi, dbgiaydep.loaisanpham , dbgiaydep.nhomsanpham WHERE dbgiaydep.nhaphanphoi.MaNhaPP = dbgiaydep.dausanpham.MaNhaPP AND dbgiaydep.dausanpham.MaLoaiSanPham = dbgiaydep.loaisanpham.MaLoaiSanPham AND dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<DSSanPham> dsDSSanPham = new List<DSSanPham>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSanPham temp = new DSSanPham();
                    temp.MaNhomSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenNhaPP = ds.Tables[0].Rows[i][3].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.MauSac = ds.Tables[0].Rows[i][5].ToString();
                    temp.KichThuoc = float.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.SoLuongHienTai = int.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Danh Sach Ma Loai Nhan Vien
        public List<LoaiNhanVien> SelectAllDSLoaiNV()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.loainhanvien";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<LoaiNhanVien> dsLoaiNhanVien = new List<LoaiNhanVien>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    LoaiNhanVien temp = new LoaiNhanVien();

                    temp.MaLoaiNhanVien = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenLoaiNhanVien = ds.Tables[0].Rows[i][1].ToString();
                    temp.TinhTrang = Int32.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsLoaiNhanVien.Add(temp);

                }
                connection.Close();
                return dsLoaiNhanVien;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<LoaiNhanVien> SelectAllDSLoaiNV(int TinhTrang)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.loainhanvien WHERE dbgiaydep.loainhanvien.TinhTrang=" +
                      TinhTrang.ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<LoaiNhanVien> dsLoaiNhanVien = new List<LoaiNhanVien>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    LoaiNhanVien temp = new LoaiNhanVien();

                    temp.MaLoaiNhanVien = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenLoaiNhanVien = ds.Tables[0].Rows[i][1].ToString();
                    temp.TinhTrang = Int32.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsLoaiNhanVien.Add(temp);

                }
                connection.Close();
                return dsLoaiNhanVien;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public string GetMaLoaiNV(string TenLoaiNV)
        {
            string MaLoaiNV;
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.loainhanvien WHERE dbgiaydep.loainhanvien.TenLoaiNhanVien='" + TenLoaiNV +
                      "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();


            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                MaLoaiNV = ds.Tables[0].Rows[0][0].ToString();
                connection.Close();
                return MaLoaiNV;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public string GetTenLoaiNV(string MaLoaiNV)
        {
            string TenLoaiNV;
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.loainhanvien.TenLoaiNhanVien FROM dbgiaydep.loainhanvien WHERE dbgiaydep.loainhanvien.MaLoaiNhanVien='" + MaLoaiNV +
                      "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();


            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                TenLoaiNV = ds.Tables[0].Rows[0][0].ToString();
                connection.Close();
                return TenLoaiNV;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Nhan Vien

        public List<NhanVien> SelectAllDSNhanVien()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.nhanvien";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<NhanVien> dsNhanVien = new List<NhanVien>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    NhanVien temp = new NhanVien();

                    temp.MaNhanVien = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaLoaiNhanVien = GetTenLoaiNV(ds.Tables[0].Rows[i][1].ToString());
                    temp.TenNhanVien = ds.Tables[0].Rows[i][2].ToString();
                    temp.TinhTrang = Int32.Parse(ds.Tables[0].Rows[i][3].ToString());
                    switch (temp.TinhTrang)
                    {
                        case 0:
                            temp.YNghiaTinhTrang = "Nghỉ việc";
                            break;
                        case 1:
                            temp.YNghiaTinhTrang = "Đang Làm";
                            break;
                    }

                    dsNhanVien.Add(temp);
                }
                connection.Close();
                return dsNhanVien;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<NhanVien> SelectAllDSNhanVien(int TinhTrang)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.nhanvien WHERE nhanvien.TinhTrang=" + TinhTrang.ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<NhanVien> dsNhanVien = new List<NhanVien>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    NhanVien temp = new NhanVien();

                    temp.MaNhanVien = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaLoaiNhanVien = GetTenLoaiNV(ds.Tables[0].Rows[i][1].ToString());
                    temp.TenNhanVien = ds.Tables[0].Rows[i][2].ToString();
                    temp.TinhTrang = Int32.Parse(ds.Tables[0].Rows[i][3].ToString());
                    switch (temp.TinhTrang)
                    {
                        case 0:
                            temp.YNghiaTinhTrang = "Nghỉ việc";
                            break;
                        case 1:
                            temp.YNghiaTinhTrang = "Đang Làm";
                            break;
                    }
                    dsNhanVien.Add(temp);
                }
                connection.Close();
                return dsNhanVien;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<NhanVien> SelectAllDSTLNhanVien(string MaLoaiNV)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.nhanvien WHERE nhanvien.MaLoaiNhanVien='" + MaLoaiNV + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<NhanVien> dsNhanVien = new List<NhanVien>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    NhanVien temp = new NhanVien();

                    temp.MaNhanVien = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaLoaiNhanVien = GetTenLoaiNV(ds.Tables[0].Rows[i][1].ToString());
                    temp.TenNhanVien = ds.Tables[0].Rows[i][2].ToString();
                    temp.TinhTrang = Int32.Parse(ds.Tables[0].Rows[i][3].ToString());

                    switch (temp.TinhTrang)
                    {
                        case 0:
                            temp.YNghiaTinhTrang = "Nghỉ việc";
                            break;
                        case 1:
                            temp.YNghiaTinhTrang = "Đang Làm";
                            break;
                    }

                    dsNhanVien.Add(temp);
                }
                connection.Close();
                return dsNhanVien;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Danh Sach San Pham Mua Ban
        public List<SanPhamMuaBan> SelectAllSanPhamMuaBan()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.dausanpham.GiaBan FROM dbgiaydep.dausanpham,dbgiaydep.nhomsanpham, dbgiaydep.sanpham WHERE dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<SanPhamMuaBan> dsDSSanPham = new List<SanPhamMuaBan>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    SanPhamMuaBan temp = new SanPhamMuaBan();
                    temp.MaSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsDSSanPham.Add(temp);
                }
                connection.Close();
                return dsDSSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<SanPhamMuaBan> SelectAllSanPhamMuaBan(int m_iFrom, int m_iTo)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.dausanpham.GiaBan FROM dbgiaydep.dausanpham, dbgiaydep.nhomsanpham, dbgiaydep.sanpham WHERE dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham AND dbgiaydep.sanpham.TinhTrang = '1' LIMIT " + m_iFrom + "," + (m_iTo - m_iFrom + 1).ToString();
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<SanPhamMuaBan> dsSanPhamMuaBan = new List<SanPhamMuaBan>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    SanPhamMuaBan temp = new SanPhamMuaBan();
                    temp.MaSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsSanPhamMuaBan.Add(temp);
                }
                connection.Close();
                return dsSanPhamMuaBan;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public SanPhamMuaBan SelectAllSanPhamMuaBan_MaSP(string m_sMaSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.dausanpham.GiaBan FROM dbgiaydep.dausanpham, dbgiaydep.nhomsanpham, dbgiaydep.sanpham WHERE dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham AND dbgiaydep.sanpham.TinhTrang = '1' AND dbgiaydep.sanpham.MaSanPham = '" + m_sMaSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                SanPhamMuaBan temp = new SanPhamMuaBan();
                temp.MaSanPham = ds.Tables[0].Rows[0][0].ToString();
                temp.TenDauSanPham = ds.Tables[0].Rows[0][1].ToString();
                temp.GiaBan = int.Parse(ds.Tables[0].Rows[0][2].ToString());

                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<SanPhamMuaBan> SelectAllSanPhamDaMua(string m_sMaHoaDon)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.ct_hoadon.DonGiaBan FROM dbgiaydep.dausanpham, dbgiaydep.nhomsanpham, dbgiaydep.sanpham, dbgiaydep.ct_hoadon WHERE dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham AND dbgiaydep.sanpham.MaSanPham = dbgiaydep.ct_hoadon.MaSanPham AND  dbgiaydep.ct_hoadon.MaHoaDon = '" + m_sMaHoaDon + "' AND dbgiaydep.sanpham.TinhTrang = 0";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<SanPhamMuaBan> dsSPMB = new List<SanPhamMuaBan>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    SanPhamMuaBan temp = new SanPhamMuaBan();
                    temp.MaSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsSPMB.Add(temp);
                }
                connection.Close();
                return dsSPMB;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<SanPhamMuaBan> SelectAllSanPhamDaMua(string m_sMaHoaDon, string m_sMaSPDaMua)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.ct_hoadon.DonGiaBan FROM dbgiaydep.dausanpham, dbgiaydep.nhomsanpham, dbgiaydep.sanpham, dbgiaydep.ct_hoadon WHERE dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham AND dbgiaydep.sanpham.MaSanPham = dbgiaydep.ct_hoadon.MaSanPham AND dbgiaydep.ct_hoadon.MaHoaDon = '" + m_sMaHoaDon + "' AND dbgiaydep.ct_hoadon.MaSanPham = '" + m_sMaSPDaMua + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<SanPhamMuaBan> dsSPMB = new List<SanPhamMuaBan>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    SanPhamMuaBan temp = new SanPhamMuaBan();
                    temp.MaSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][2].ToString());

                    dsSPMB.Add(temp);
                }
                connection.Close();
                return dsSPMB;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Ma Loai NV theo NV
        public string SelectMaLoaiNV_MaNV(string m_sMaNV)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhanvien.MaLoaiNhanVien FROM dbgiaydep.nhanvien WHERE dbgiaydep.nhanvien.TinhTrang = '1' AND dbgiaydep.nhanvien.MaNhanVien = '" + m_sMaNV + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();

                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Phieu Tra San Pham
        public List<PhieuTra_CTPhieuTra> SelectAllPhieuTraSanPhamLoi()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.phieutrahang.MaPhieuTraHang, dbgiaydep.phieutrahang.NgayTra, dbgiaydep.ct_phieutrahang.MaSanPham, dbgiaydep.ct_phieutrahang.ThongTinLoiSP FROM dbgiaydep.ct_phieutrahang, dbgiaydep.phieutrahang WHERE dbgiaydep.ct_phieutrahang.MaPhieuTra = dbgiaydep.phieutrahang.MaPhieuTraHang ";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<PhieuTra_CTPhieuTra> dsDSPhieuTra = new List<PhieuTra_CTPhieuTra>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    PhieuTra_CTPhieuTra temp = new PhieuTra_CTPhieuTra();

                    temp.MaPhieuTraHang = ds.Tables[0].Rows[i][0].ToString();
                    temp.NgayTra = DateTime.Parse(ds.Tables[0].Rows[i][1].ToString());
                    temp.MaSanPham = ds.Tables[0].Rows[i][2].ToString();
                    temp.ThongTinLoiSP = ds.Tables[0].Rows[i][3].ToString();

                    dsDSPhieuTra.Add(temp);
                }
                connection.Close();
                return dsDSPhieuTra;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select San Pham
        public SanPham SelectAllSanPham(string m_sMaSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.sanpham WHERE MaSanPham = '" + m_sMaSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                SanPham temp = new SanPham();

                temp.MaSanPham = ds.Tables[0].Rows[0][0].ToString();
                temp.MaNhomSanPham = ds.Tables[0].Rows[0][1].ToString();
                temp.MaNhapHang = ds.Tables[0].Rows[0][2].ToString();
                temp.TinhTrang = Int32.Parse(ds.Tables[0].Rows[0][3].ToString());

                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Ma Dau SP theo Ma SP
        public string SelectMaDauSanPham_MaSP(string m_sMaSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.dausanpham.MaDauSanPham FROM dbgiaydep.sanpham, dbgiaydep.nhomsanpham, dbgiaydep.dausanpham WHERE dbgiaydep.sanpham.MaNhomSanPham = dbgiaydep.nhomsanpham.MaNhomSanPham AND dbgiaydep.nhomsanpham.MaDauSanPham = dbgiaydep.dausanpham.MaDauSanPham AND MaSanPham = '" + m_sMaSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Nhan vien - Ma NV
        public NhanVien SelectNhanVien_MaNV(string m_sMaNV)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT * FROM dbgiaydep.nhanvien WHERE dbgiaydep.nhanvien.MaNhanVien = '" + m_sMaNV + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                NhanVien temp = new NhanVien();
                temp.MaNhanVien = ds.Tables[0].Rows[0][0].ToString();
                temp.MaLoaiNhanVien = ds.Tables[0].Rows[0][1].ToString();
                temp.TenNhanVien = ds.Tables[0].Rows[0][2].ToString();
                temp.TinhTrang = int.Parse(ds.Tables[0].Rows[0][3].ToString());
                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select Ten Loai NV
        public string SelectTenLoaiNV(string m_sMaLoaiNV)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.loainhanvien.TenLoaiNhanVien FROM dbgiaydep.loainhanvien WHERE dbgiaydep.loainhanvien.MaLoaiNhanVien = '" + m_sMaLoaiNV + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                string temp;
                temp = ds.Tables[0].Rows[0][0].ToString();
                connection.Close();
                return temp;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Select San Pham Ban Theo Ngay
        public List<SanPhamMuaBan> SelectAllSanPhamDaMuaTheoNgay(string m_dtNgayBaoCao)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.ct_hoadon.DonGiaBan, dbgiaydep.ct_hoadon.MaHoaDon FROM dbgiaydep.dausanpham, dbgiaydep.nhomsanpham, dbgiaydep.sanpham, dbgiaydep.ct_hoadon, dbgiaydep.hoadon WHERE dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham AND dbgiaydep.sanpham.MaSanPham = dbgiaydep.ct_hoadon.MaSanPham AND  dbgiaydep.hoadon.MaHoaDon = dbgiaydep.ct_hoadon.MaHoaDon AND dbgiaydep.hoadon.NgayBan = '" + m_dtNgayBaoCao + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<SanPhamMuaBan> dsSPMB = new List<SanPhamMuaBan>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    SanPhamMuaBan temp = new SanPhamMuaBan();
                    temp.MaSanPham = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSanPham = ds.Tables[0].Rows[i][1].ToString();
                    temp.GiaBan = int.Parse(ds.Tables[0].Rows[i][2].ToString());
                    temp.MaHoaDon = ds.Tables[0].Rows[i][3].ToString();

                    dsSPMB.Add(temp);
                }
                connection.Close();
                return dsSPMB;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public DataSet SelectAllSanPhamDaMuaTheoNgay_DataSet(string m_dtNgayBaoCao)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.sanpham.MaSanPham, dbgiaydep.dausanpham.TenDauSanPham, dbgiaydep.ct_hoadon.DonGiaBan, dbgiaydep.ct_hoadon.MaHoaDon FROM dbgiaydep.dausanpham, dbgiaydep.nhomsanpham, dbgiaydep.sanpham, dbgiaydep.ct_hoadon, dbgiaydep.hoadon WHERE dbgiaydep.dausanpham.MaDauSanPham = dbgiaydep.nhomsanpham.MaDauSanPham AND dbgiaydep.nhomsanpham.MaNhomSanPham = dbgiaydep.sanpham.MaNhomSanPham AND dbgiaydep.sanpham.MaSanPham = dbgiaydep.ct_hoadon.MaSanPham AND  dbgiaydep.hoadon.MaHoaDon = dbgiaydep.ct_hoadon.MaHoaDon AND dbgiaydep.hoadon.NgayBan = '" + m_dtNgayBaoCao + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);
                connection.Close();
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        public int SelectShoLuongNhomSP(string m_sMaNhomSP)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.setConnectionString());
            cmdtext = "SELECT dbgiaydep.nhomsanpham.SoLuongHienTai FROM dbgiaydep.nhomsanpham WHERE dbgiaydep.nhomsanpham.MaNhomSanPham = '" + m_sMaNhomSP + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                int temp = int.Parse(ds.Tables[0].Rows[0][0].ToString());

                return temp;
            }
            catch
            {
                return -1;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
    }
}
